package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xmy.cultivate.entity.CancellationRate;
import com.xmy.cultivate.entity.OrderApply;
import com.xmy.cultivate.entity.Staff;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.StaffStatusCount;
import com.xmy.cultivate.respons.FlowTeacherRs;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * <p>
 * 员工 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-07-26
 */
@Mapper
public interface StaffMapper extends BaseMapper<Staff> {

    @Select("SELECT * FROM staff where id=#{id} and deleted =0")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(
                    property = "adminObj",
                    column = "admin_id",
                    one = @One(select = "com.xmy.cultivate.mapper.AdminMapper.selectById")
            )
    })
    public abstract Staff findOneById(@Param("id") Long id);


    @Select("SELECT * FROM staff ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id", property = "id"),
            @Result(
                    property = "adminObj",
                    column = "admin_id",
                    one = @One(select = "com.xmy.cultivate.mapper.AdminMapper.selectById")
            )
    })
    public abstract IPage<Staff> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("SELECT * FROM staff where admin_id = #{adminId}")
    public abstract Staff selectByAdminId(@Param("adminId") Long adminId);

    @Select("SELECT id FROM staff WHERE admin_id = #{adminId}")
    public abstract Long selectIdByAdminId(@Param("adminId") Long adminId);

    @Select("SELECT id,name FROM staff WHERE school_id = #{schoolId} AND is_teacher = 1 AND status = 1 AND deleted = 0")
    public abstract List<Staff> selectIdBySchoolId(@Param("schoolId") Long schoolId);

    @Select("SELECT * FROM staff WHERE status = 1 AND deleted = 0 and is_teacher = 1")
    public abstract List<Staff> staffList();

    @Select("SELECT id,name FROM staff WHERE status = 1 AND deleted = 0 and is_teacher = 1")
    public abstract List<Staff> staffIdAndNameList();

    @Select("SELECT staff.id,staff.school_id,staff.name FROM staff LEFT JOIN admin ON admin.id=staff.admin_id LEFT JOIN role_admin ON role_admin.admin_id = admin.id WHERE role_admin.role_id=1568160887369994242")
    public abstract List<Staff> staffByHeadmaster();

    @Select("select * from staff where admin_id = ${adminId}")
    Staff getOneForAdminid(@Param("adminId") Long adminId);

    /**
     * 通过登陆账号获取教师id
     *
     * @param adminId
     * @return
     */
    @Select("SELECT staff.id,staff.name FROM admin INNER JOIN staff ON admin.id = staff.admin_id WHERE staff.deleted = 0 AND staff.`name` NOT LIKE '%校区前台%' AND admin.id = ${adminId}")
    Staff getSatffId(@Param("adminId") Long adminId);


    /**获取人事人员列表
     *
     */
//    @Select({"<script>",
//            "SELECT s.id,s.name FROM staff s JOIN role_admin ra ON s.id = ra.admin_id JOIN role r ON ra.role_id = r.id WHERE r.id=1568160915052400644",
//            "<if test='name != null and name != &apos;&apos;'>",
//            "AND s.name LIKE CONCAT('%',#{name},'%')",
//            "</if>",
//            "</script>"})
//    public List<Staff> getPersonnelList(@Param("name") String name);

    /**获取有简历数据人员列表
     *
     */
    @Select({"<script>",
            "SELECT s.id,s.name FROM adms_recruit t1 JOIN staff s ON t1.inviter_id = s.id WHERE 1=1",
            "<if test='name != null and name != &apos;&apos;'>",
            "AND s.name LIKE CONCAT('%',#{name},'%')",
            "</if>",
            "GROUP BY t1.inviter_id",
            "</script>"})
    public List<Staff> getPersonnelList(@Param("name") String name);

    @Select("select * from staff where id in(${checkIds})")
    public List<FlowTeacherRs> getFlowTeacherList(@Param("checkIds") String checkIds);

    @Select({
            "<script>",
            "SELECT",
            "   s.school_id AS school_id,",
            "   getSchoolName(s.school_id) AS school_name,",
            "   SUM(CASE WHEN s.status = 1 THEN 1 ELSE 0 END) AS working_count,",
            "   SUM(CASE WHEN s.status = 2 THEN 1 ELSE 0 END) AS leave_count",
            "FROM",
            "   staff s",
            "   LEFT JOIN (",
            "       SELECT",
            "           teacher_id,",
            "           GROUP_CONCAT(DISTINCT year_class_id) AS year_class_id,",
            "           GROUP_CONCAT(DISTINCT subjects_id) AS subjects_id",
            "       FROM (",
            "           SELECT",
            "               t01.*",
            "           FROM (",
            "               SELECT",
            "                   t1.id,",
            "                   t1.name,",
            "                   t2.id AS quarter_id,",
            "                   t1.teacher_id,",
            "                   t1.year_class_id,",
            "                   t1.subjects_id",
            "               FROM",
            "                   grade AS t1",
            "                   INNER JOIN QUARTER AS t2 ON t1.year_part = t2.year_part",
            "                   AND t1.quarter_num = t2.num",
            "               WHERE",
            "                   t1.deleted = 0",
            "           ) AS t01",
            "           INNER JOIN (",
            "               SELECT",
            "                   t1.id,",
            "                   t1.name,",
            "                   MAX(t2.id) AS quarter_id,",
            "                   t1.teacher_id",
            "               FROM",
            "                   grade AS t1",
            "                   INNER JOIN QUARTER AS t2 ON t1.year_part = t2.year_part",
            "                   AND t1.quarter_num = t2.num",
            "               WHERE",
            "                   t1.deleted = 0",
            "               GROUP BY",
            "                   t1.teacher_id",
            "           ) AS t02 ON t01.teacher_id = t02.teacher_id",
            "           AND t01.quarter_id = t02.quarter_id",
            "       ) AS t",
            "       GROUP BY",
            "           teacher_id",
            "   ) AS teacher_classes ON s.id = teacher_classes.teacher_id",
            "   LEFT JOIN (",
            "       SELECT teacher_id, GROUP_CONCAT(year_class_id) AS year_class_id",
            "       FROM teacher_leads_year_class",
            "   ) tc ON s.id = tc.teacher_id",
            "WHERE",
            "   s.is_teacher = 1",
            "   AND s.deleted = 0",
            "   <if test='yearClassId!=&apos;&apos; and yearClassId.size() > 0'>",
            "   AND (",
            "       <foreach item='yearClassId' index='index' collection='yearClassId' open='(' separator=' OR ' close=')'>",
            "           FIND_IN_SET(${yearClassId}, teacher_classes.year_class_id)",
            "       </foreach>",
            "       OR",
            "       <foreach item='yearClassId' index='index' collection='yearClassId' open='(' separator=' OR ' close=')'>",
            "           FIND_IN_SET(${yearClassId}, tc.year_class_id)",
            "       </foreach>",
            "   )",
            "   </if>",
            "   <if test='subjectsId!=&apos;&apos; and subjectsId.size() > 0'>",
            "   AND (",
            "       (",
            "           teacher_classes.subjects_id IS NOT NULL",
            "           AND (",
            "               <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "                   FIND_IN_SET(#{subjectsIdItem}, teacher_classes.subjects_id)",
            "               </foreach>",
            "               OR",
            "               <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "                   FIND_IN_SET(#{subjectsIdItem}, s.sigin_subjects)",
            "               </foreach>",
            "           )",
            "       )",
            "       OR (teacher_classes.subjects_id IS NULL AND (",
            "           <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "               FIND_IN_SET(#{subjectsIdItem}, s.sigin_subjects)",
            "           </foreach>",
            "       ))",
            "   )",
            "   </if>",
            "GROUP BY",
            "   s.school_id",
            "</script>"
    })
    public List<CancellationRate> getStaffStatusCountList(@Param("yearClassId") List yearClassId, @Param("subjectsId") List subjectsId);

    @Select({
            "<script>",
            "SELECT",
            "   s.school_id AS school_id,",
            "   getSchoolName(s.school_id) AS school_name,",
            "   s.name AS staff_name",
            "FROM",
            "   staff s",
            "   LEFT JOIN (",
            "       SELECT",
            "           teacher_id,",
            "           GROUP_CONCAT(DISTINCT year_class_id) AS year_class_id,",
            "           GROUP_CONCAT(DISTINCT subjects_id) AS subjects_id",
            "       FROM (",
            "           SELECT",
            "               t01.*",
            "           FROM (",
            "               SELECT",
            "                   t1.id,",
            "                   t1.name,",
            "                   t2.id AS quarter_id,",
            "                   t1.teacher_id,",
            "                   t1.year_class_id,",
            "                   t1.subjects_id",
            "               FROM",
            "                   grade AS t1",
            "                   INNER JOIN QUARTER AS t2 ON t1.year_part = t2.year_part",
            "                   AND t1.quarter_num = t2.num",
            "               WHERE",
            "                   t1.deleted = 0",
            "           ) AS t01",
            "           INNER JOIN (",
            "               SELECT",
            "                   t1.id,",
            "                   t1.name,",
            "                   MAX(t2.id) AS quarter_id,",
            "                   t1.teacher_id",
            "               FROM",
            "                   grade AS t1",
            "                   INNER JOIN QUARTER AS t2 ON t1.year_part = t2.year_part",
            "                   AND t1.quarter_num = t2.num",
            "               WHERE",
            "                   t1.deleted = 0",
            "               GROUP BY",
            "                   t1.teacher_id",
            "           ) AS t02 ON t01.teacher_id = t02.teacher_id",
            "           AND t01.quarter_id = t02.quarter_id",
            "       ) AS t",
            "       GROUP BY",
            "           teacher_id",
            "   ) AS teacher_classes ON s.id = teacher_classes.teacher_id",
            "   LEFT JOIN (",
            "       SELECT teacher_id, GROUP_CONCAT(year_class_id) AS year_class_id",
            "       FROM teacher_leads_year_class",
            "   ) tc ON s.id = tc.teacher_id",
            "WHERE",
            "   s.is_teacher = 1",
            "   AND s.deleted = 0",
            "   <if test='schoolId!=&apos;&apos;'>",
            "   AND s.school_id IN ( ${schoolId} )",
            "   </if>",
            "   <if test='status!=&apos;&apos;'>",
            "   AND s.status = ${status}",
            "   </if>",
            "   <if test='yearClassId!=&apos;&apos; and yearClassId.size() > 0'>",
            "   AND (",
            "       <foreach item='yearClassId' index='index' collection='yearClassId' open='(' separator=' OR ' close=')'>",
            "           FIND_IN_SET(${yearClassId}, teacher_classes.year_class_id)",
            "       </foreach>",
            "       OR",
            "       <foreach item='yearClassId' index='index' collection='yearClassId' open='(' separator=' OR ' close=')'>",
            "           FIND_IN_SET(${yearClassId}, tc.year_class_id)",
            "       </foreach>",
            "   )",
            "   </if>",
            "   <if test='subjectsId!=&apos;&apos; and subjectsId.size() > 0'>",
            "   AND (",
            "       (",
            "           teacher_classes.subjects_id IS NOT NULL",
            "           AND (",
            "               <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "                   FIND_IN_SET(#{subjectsIdItem}, teacher_classes.subjects_id)",
            "               </foreach>",
            "               OR",
            "               <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "                   FIND_IN_SET(#{subjectsIdItem}, s.sigin_subjects)",
            "               </foreach>",
            "           )",
            "       )",
            "       OR (teacher_classes.subjects_id IS NULL AND (",
            "           <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "               FIND_IN_SET(#{subjectsIdItem}, s.sigin_subjects)",
            "           </foreach>",
            "       ))",
            "   )",
            "   </if>",
//            "GROUP BY",
//            "   s.school_id",
            "</script>"
    })
    public List<StaffStatusCount> getStaffStatusDetails(@Param("yearClassId") List yearClassId, @Param("subjectsId") List subjectsId, @Param("schoolId") String schoolId, @Param("status") String status);

    @Select({
            "<script>",
            "SELECT",
            "   s.school_id AS school_id,",
            "   getSchoolName(s.school_id) AS school_name,",
            "   s.name AS staff_name",
            "FROM",
            "   staff s",
            "   LEFT JOIN (",
            "       SELECT",
            "           teacher_id,",
            "           year_class_id AS year_class_id,",
            "           GROUP_CONCAT(DISTINCT subjects_id) AS subjects_id",
            "       FROM (",
            "           SELECT",
            "               t01.*",
            "           FROM (",
            "               SELECT",
            "                   t1.id,",
            "                   t1.name,",
            "                   t2.id AS quarter_id,",
            "                   t1.teacher_id,",
            "                   t1.year_class_id,",
            "                   t1.subjects_id",
            "               FROM",
            "                   grade AS t1",
            "                   INNER JOIN QUARTER AS t2 ON t1.year_part = t2.year_part",
            "                   AND t1.quarter_num = t2.num",
            "               WHERE",
            "                   t1.deleted = 0",
            "           ) AS t01",
            "           INNER JOIN (",
            "               SELECT",
            "                   t1.id,",
            "                   t1.name,",
            "                   MAX(t2.id) AS quarter_id,",
            "                   t1.teacher_id",
            "               FROM",
            "                   grade AS t1",
            "                   INNER JOIN QUARTER AS t2 ON t1.year_part = t2.year_part",
            "                   AND t1.quarter_num = t2.num",
            "               WHERE",
            "                   t1.deleted = 0",
            "               GROUP BY",
            "                   t1.teacher_id,year_class_id ",
            "           ) AS t02 ON t01.teacher_id = t02.teacher_id",
            "           AND t01.quarter_id = t02.quarter_id",
            "       ) AS t",
            "       GROUP BY",
            "           teacher_id,year_class_id",
            "   ) AS teacher_classes ON s.id = teacher_classes.teacher_id",
            "   LEFT JOIN (",
            "       SELECT teacher_id, GROUP_CONCAT(year_class_id) AS year_class_id",
            "       FROM teacher_leads_year_class",
            "   ) tc ON s.id = tc.teacher_id",
            "WHERE",
            "   s.is_teacher = 1",
            "   AND s.deleted = 0",
            "   AND teacher_classes.year_class_id IS NOT NULL ",
            "   <if test='yearClassId!=&apos;&apos;'>",
            "   AND teacher_classes.year_class_id = ${yearClassId}",
            "   </if>",
            "   <if test='status!=&apos;&apos;'>",
            "   AND s.status = ${status}",
            "   </if>",
//            "   <if test='yearClassId!=&apos;&apos; and yearClassId.size() > 0'>",
//            "   AND (",
//            "       <foreach item='yearClassId' index='index' collection='yearClassId' open='(' separator=' OR ' close=')'>",
//            "           FIND_IN_SET(${yearClassId}, teacher_classes.year_class_id)",
//            "       </foreach>",
//            "       OR",
//            "       <foreach item='yearClassId' index='index' collection='yearClassId' open='(' separator=' OR ' close=')'>",
//            "           FIND_IN_SET(${yearClassId}, tc.year_class_id)",
//            "       </foreach>",
//            "   )",
//            "   </if>",
            "   <if test='subjectsId!=&apos;&apos; and subjectsId.size() > 0'>",
            "   AND (",
            "       (",
            "           teacher_classes.subjects_id IS NOT NULL",
            "           AND (",
            "               <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "                   FIND_IN_SET(#{subjectsIdItem}, teacher_classes.subjects_id)",
            "               </foreach>",
            "               OR",
            "               <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "                   FIND_IN_SET(#{subjectsIdItem}, s.sigin_subjects)",
            "               </foreach>",
            "           )",
            "       )",
            "       OR (teacher_classes.subjects_id IS NULL AND (",
            "           <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "               FIND_IN_SET(#{subjectsIdItem}, s.sigin_subjects)",
            "           </foreach>",
            "       ))",
            "   )",
            "   </if>",
//            "GROUP BY",
//            "   s.school_id",
            "</script>"
    })
    public List<StaffStatusCount> getStaffStatusGradeDetails(@Param("yearClassId") String yearClassId, @Param("subjectsId") List subjectsId, @Param("status") String status);


    @Select({
            "<script>",
            "SELECT",
            "   getSchoolName ( t2.school_id ) AS school_name,",
            "   t2.working_count,",
            "   t2.leave_count,",
            "   t2.school_id,",
            "   t1.pid AS schoolPid",
            "FROM",
            "( SELECT school_id,school_name,pid FROM school_quarter WHERE deleted = 0 and id &lt;&gt; pid GROUP BY school_id) AS t1",
            "INNER JOIN (",
            "SELECT",
            "   s.school_id AS school_id,",
            "   getSchoolName(s.school_id) AS school_name,",
            "   SUM(CASE WHEN s.status = 1 THEN 1 ELSE 0 END) AS working_count,",
            "   SUM(CASE WHEN s.status = 2 THEN 1 ELSE 0 END) AS leave_count",
            "FROM",
            "   staff s",
            "   LEFT JOIN (",
            "       SELECT",
            "           teacher_id,",
            "           GROUP_CONCAT(DISTINCT year_class_id) AS year_class_id,",
            "           GROUP_CONCAT(DISTINCT subjects_id) AS subjects_id",
            "       FROM (",
            "           SELECT",
            "               t01.*",
            "           FROM (",
            "               SELECT",
            "                   t1.id,",
            "                   t1.name,",
            "                   t2.id AS quarter_id,",
            "                   t1.teacher_id,",
            "                   t1.year_class_id,",
            "                   t1.subjects_id",
            "               FROM",
            "                   grade AS t1",
            "                   INNER JOIN QUARTER AS t2 ON t1.year_part = t2.year_part",
            "                   AND t1.quarter_num = t2.num",
            "               WHERE",
            "                   t1.deleted = 0",
            "           ) AS t01",
            "           INNER JOIN (",
            "               SELECT",
            "                   t1.id,",
            "                   t1.name,",
            "                   MAX(t2.id) AS quarter_id,",
            "                   t1.teacher_id",
            "               FROM",
            "                   grade AS t1",
            "                   INNER JOIN QUARTER AS t2 ON t1.year_part = t2.year_part",
            "                   AND t1.quarter_num = t2.num",
            "               WHERE",
            "                   t1.deleted = 0",
            "               GROUP BY",
            "                   t1.teacher_id",
            "           ) AS t02 ON t01.teacher_id = t02.teacher_id",
            "           AND t01.quarter_id = t02.quarter_id",
            "       ) AS t",
            "       GROUP BY",
            "           teacher_id",
            "   ) AS teacher_classes ON s.id = teacher_classes.teacher_id",
            "   LEFT JOIN (",
            "       SELECT teacher_id, GROUP_CONCAT(year_class_id) AS year_class_id",
            "       FROM teacher_leads_year_class",
            "   ) tc ON s.id = tc.teacher_id",
            "WHERE",
            "   s.is_teacher = 1",
            "   AND s.deleted = 0",
            "   <if test='yearClassId!=&apos;&apos; and yearClassId.size() > 0'>",
            "   AND (",
            "       <foreach item='yearClassId' index='index' collection='yearClassId' open='(' separator=' OR ' close=')'>",
            "           FIND_IN_SET(${yearClassId}, teacher_classes.year_class_id)",
            "       </foreach>",
            "       OR",
            "       <foreach item='yearClassId' index='index' collection='yearClassId' open='(' separator=' OR ' close=')'>",
            "           FIND_IN_SET(${yearClassId}, tc.year_class_id)",
            "       </foreach>",
            "   )",
            "   </if>",
            "   <if test='subjectsId!=&apos;&apos; and subjectsId.size() > 0'>",
            "   AND (",
            "       (",
            "           teacher_classes.subjects_id IS NOT NULL",
            "           AND (",
            "               <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "                   FIND_IN_SET(#{subjectsIdItem}, teacher_classes.subjects_id)",
            "               </foreach>",
            "               OR",
            "               <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "                   FIND_IN_SET(#{subjectsIdItem}, s.sigin_subjects)",
            "               </foreach>",
            "           )",
            "       )",
            "       OR (teacher_classes.subjects_id IS NULL AND (",
            "           <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "               FIND_IN_SET(#{subjectsIdItem}, s.sigin_subjects)",
            "           </foreach>",
            "       ))",
            "   )",
            "   </if>",
            "GROUP BY",
            "   s.school_id",
            ") AS t2 ON t1.school_id = t2.school_id",
            "GROUP BY t1.school_id",
            "</script>"
    })
    public List<CancellationRate> getStaffStatusCountByFourSchoolList(@Param("yearClassId") List yearClassId, @Param("subjectsId") List subjectsId);




    @Select({
            "<script>",
            "SELECT",
            "   teacher_classes.year_class_id,",
            "   SUM(CASE WHEN s.status = 1 THEN 1 ELSE 0 END) AS working_count,",
            "   SUM(CASE WHEN s.status = 2 THEN 1 ELSE 0 END) AS leave_count",
            "FROM",
            "   staff s",
            "   LEFT JOIN (",
            "       SELECT",
            "           teacher_id,",
            "           year_class_id AS year_class_id,",
            "           GROUP_CONCAT(DISTINCT subjects_id) AS subjects_id",
            "       FROM (",
            "           SELECT",
            "               t01.*",
            "           FROM (",
            "               SELECT",
            "                   t1.id,",
            "                   t1.name,",
            "                   t2.id AS quarter_id,",
            "                   t1.teacher_id,",
            "                   t1.year_class_id,",
            "                   t1.subjects_id",
            "               FROM",
            "                   grade AS t1",
            "                   INNER JOIN QUARTER AS t2 ON t1.year_part = t2.year_part",
            "                   AND t1.quarter_num = t2.num",
            "               WHERE",
            "                   t1.deleted = 0",
            "           ) AS t01",
            "           INNER JOIN (",
            "               SELECT",
            "                   t1.id,",
            "                   t1.name,",
            "                   MAX(t2.id) AS quarter_id,",
            "                   t1.teacher_id",
            "               FROM",
            "                   grade AS t1",
            "                   INNER JOIN QUARTER AS t2 ON t1.year_part = t2.year_part",
            "                   AND t1.quarter_num = t2.num",
            "               WHERE",
            "                   t1.deleted = 0",
            "               GROUP BY",
            "                   t1.teacher_id ,year_class_id",
            "           ) AS t02 ON t01.teacher_id = t02.teacher_id",
            "           AND t01.quarter_id = t02.quarter_id",
            "       ) AS t",
            "       GROUP BY",
            "           teacher_id ,year_class_id",
            "   ) AS teacher_classes ON s.id = teacher_classes.teacher_id",
            "   LEFT JOIN (",
            "       SELECT teacher_id, GROUP_CONCAT(year_class_id) AS year_class_id",
            "       FROM teacher_leads_year_class",
            "   ) tc ON s.id = tc.teacher_id",
            "WHERE",
            "   s.is_teacher = 1",
            "   AND s.deleted = 0",
            "AND teacher_classes.year_class_id IS NOT NULL",
//            "   <if test='yearClassId!=&apos;&apos; and yearClassId.size() > 0'>",
//            "   AND (",
//            "       <foreach item='yearClassId' index='index' collection='yearClassId' open='(' separator=' OR ' close=')'>",
//            "           FIND_IN_SET(${yearClassId}, teacher_classes.year_class_id)",
//            "       </foreach>",
//            "       OR",
//            "       <foreach item='yearClassId' index='index' collection='yearClassId' open='(' separator=' OR ' close=')'>",
//            "           FIND_IN_SET(${yearClassId}, tc.year_class_id)",
//            "       </foreach>",
//            "   )",
//            "   </if>",
            "   <if test='subjectsId!=&apos;&apos; and subjectsId.size() > 0'>",
            "   AND (",
            "       (",
            "           teacher_classes.subjects_id IS NOT NULL",
            "           AND (",
            "               <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "                   FIND_IN_SET(#{subjectsIdItem}, teacher_classes.subjects_id)",
            "               </foreach>",
            "               OR",
            "               <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "                   FIND_IN_SET(#{subjectsIdItem}, s.sigin_subjects)",
            "               </foreach>",
            "           )",
            "       )",
            "       OR (teacher_classes.subjects_id IS NULL AND (",
            "           <foreach item='subjectsIdItem' index='index' collection='subjectsId' open='' separator=' OR ' close=''>",
            "               FIND_IN_SET(#{subjectsIdItem}, s.sigin_subjects)",
            "           </foreach>",
            "       ))",
            "   )",
            "   </if>",
            "GROUP BY",
            "   teacher_classes.year_class_id",
            "ORDER BY teacher_classes.year_class_id ",
            "</script>"
    })
    public List<CancellationRate> getStaffStatusCountByGradeList( @Param("subjectsId") List subjectsId);

    @Select({
            "<script>",
            "SELECT `name` FROM staff WHERE id = ${id}",
            "</script>"
    })
    public String getStaffNameById(@Param("id") Long id);

}

